CREATE PROCEDURE [dbo].[spAPI_CheckToken]
(
	@Token VARCHAR(255),
	@Result INT OUT,
	@UserId INT = NULL OUT,
	@BusinessId INT = NULL OUT
)
AS
BEGIN
	SET NOCOUNT ON
	
	DECLARE @ExpirationDate DATETIME
	SET @Result = 0
	
	SELECT @ExpirationDate = UserToken.ExpirationDate, @UserId = [User].UserId, @BusinessId = Business.BusinessId
	FROM UserToken LEFT JOIN [User] ON UserToken.LoginName = [User].[Login]
					LEFT JOIN Business ON UserToken.LoginName = Business.[Login]
	WHERE UserToken.SecurityToken = @Token
	
	IF (@ExpirationDate IS NOT NULL)
	BEGIN
		IF (GETDATE() > @ExpirationDate)
		BEGIN
			DELETE 
			FROM UserToken 
			WHERE SecurityToken = @Token
			
			SET @UserId = NULL
			SET @BusinessId = NULL
		END
		ELSE
			SET @Result = 1
	END
END
GO
CREATE PROCEDURE [dbo].[spAPI_BusinessReview_GetList]
(
	@Token VARCHAR(255),
	@BusinessId INT
)
AS
BEGIN
	SET NOCOUNT ON
	
	DECLARE @IdResult INT
	DECLARE @Result XML
	
	SET @IdResult = 0
	EXEC spAPI_CheckToken @Token = @Token, @Result = @IdResult OUT
	
	IF @IdResult = 1
	BEGIN
		DECLARE @Likes INT, @Dislikes INT
		
		SELECT @Likes = SUM(CASE WHEN IsLiked = 1 THEN 1 ELSE 0 END),
			@Dislikes = SUM(CASE WHEN IsLiked = 0 THEN 1 ELSE 0 END)
		FROM BusinessLike
		WHERE BusinessId = @BusinessId
				
		SELECT @Result = (SELECT *
						FROM (SELECT 1 AS Tag, NULL AS Parent, @Likes AS [Reviews!1!Likes], @Dislikes AS [Reviews!1!Dislikes],
								NULL AS [Review!2!UserName!ELEMENT], NULL AS [Review!2!Rating!ELEMENT], NULL AS [Review!2!Text!ELEMENT]
							
							UNION ALL
							
							SELECT 2 AS Tag, 1 AS Parent, NULL, NULL,
								[User].[Login], 
								CONVERT(VARCHAR(20), BusinessReview.Rating) + ',' + 
									CASE WHEN BusinessReview.Rating BETWEEN 1 AND 2 THEN 'Y' 
										WHEN BusinessReview.Rating BETWEEN 3 AND 4 THEN 'G' 
									ELSE 'R' END,
								BusinessReview.ReviewText
							FROM BusinessReview LEFT JOIN [User] ON BusinessReview.UserId = [User].UserId
							WHERE BusinessReview.BusinessId = @BusinessId) AS vw
						FOR XML EXPLICIT)
	END					
		
	SELECT @IdResult AS IdResult, ISNULL(@Result, '') AS Result
END
GO